﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Support.DTO;



namespace DataAccess
{
    public class DepartmentDAL : BaseSQLDAL
    {
        public List<DepartmentDTO> GetDepartments()
        {
            SqlConnection connection = null;
            SqlDataReader dataReader = null;
            SqlCommand command = null;
            List<DepartmentDTO> departments = new List<DepartmentDTO>();

            try
            {
                using (connection = new SqlConnection(this.GetConnectionString_RepComcelAdm()))
                {
                    command = new SqlCommand("usp_GetDepartamentos", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    connection.Open();
                    dataReader = command.ExecuteReader();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            departments.Add(this.FillDTO(dataReader));
                        }
                    }

                    return departments;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                command.Dispose();
                dataReader.Dispose();
                connection.Close();
                connection.Dispose();
            }
        }

        private DepartmentDTO FillDTO(SqlDataReader reader)
        {
            int id = reader.GetInt32(reader.GetOrdinal("id_departamento"));
            string name = reader.GetString(reader.GetOrdinal("depto"));
            return new DepartmentDTO() { Id = id, Name = name };
        }
    }
}
